<html>
    <head></head>
    <body>  
		<table>
			<thead>
				<th>部门</th>
				<th>金额</th>
				<th>人员</th> 
				<th>case when</th> 
			</thead>
			<tbody>
				
			</tbody>
		</table>
        <script src="sqlParser.js"></script>
        <script src="sqlQueryBro.js"></script>
        <script>
			var data=[
				{depName:"研发部",money:30,user:"李白"},
				{depName:"研发部",money:50},
				{depName:"研发部",money:50,user:"王五"},
				{depName:"测试部",money:50,user:"如花"},
				{depName:"测试部",money:50,user:"小爱"},
				{depName:"测试部",money:50,user:"小李"},
				{depName:"力王",money:50,user:"小飞"},
				{depName:"力王",money:50,user:"小同"}
			];
			var toView=function(data){
				var html="";
				for(var i=0;i<data.length;i++){
					html+="<tr>";
					html+="<td>"+(data[i].depName?data[i].depName:'') +"</td>"; 
					html+="<td>"+(data[i].money?data[i].money:'')+"</td>";
					html+="<td>"+(data[i].user?data[i].user:'')+"</td>"; 
					html+="<td>"+(data[i].aaaa?data[i].aaaa:'')+"</td>"; 
					html+="</tr>";
					
				}
				return html;
			}
			var addList=function(list,list1){
				for(var i=0;i<list1.length;i++){
					list.push(list1[i]);
				}
				
			}
			sqlQuery.prototype.fn["round"]=function(val,dep){
				return val.toFixed(dep?dep:2)
			}
			var toGroup=function(){
				var list=[];
				list=data;
				var list1=sqlQuery(data,`
					select 
						concat(depName,'(小计)') as depName,
						round(sum(money),4) as  money,
						1 as num,
						count(user) as user
					from table 
					group by depName
				`);
				var list2=sqlQuery(data, `
					select 
						'合计' as depName,
						round(sum(money),4) as money,
						2 as num ,
						count(user)  as user
					from table 
				`);
				addList(list,list1);
			 
				list=sqlQuery(list,"select *,case when money>=51 then '1' else '0' end aaaa from table order by num,depName");
				addList(list,list2); 
				document.getElementsByTagName("tbody")[0].innerHTML=toView(list);
			}
			toGroup();
           
        </script>
    </body>
</html>